<?php
class monitor_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
		$this->db = $this->load->database ( 'default', true );
	}
	public function search_product($keyword, $limit) {
		$keyword = $this->db->escape_like_str ( $keyword );
		$sql = "select 		product_name,
							brand_name,
							brand_id as id
					from
							monitor inner join sys_brand on monitor.brand =sys_brand.brand_id
					where 	product_name like '%$keyword%'
					group by product_name
					order by sys_brand.brand_id asc limit 0,$limit ";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function search_brand($keyword) {
		$keyword = $this->db->escape_like_str ( $keyword );
		$sql = "select 		brand_name,
							brand_id as id
					from
							 sys_brand 
					where 	brand_name like '%$keyword%'
					order by sys_brand.brand_id asc limit 0,10  ";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function query_industry($parent_id) {
		$parent_id = $this->db->escape_str ( $parent_id );
		$sql = "select 	industry_name,
						industry_id 
				from sys_industry
				where industry_status ='1'
					and parent_id ='$parent_id'
				order by industry_id asc";
		$query = $this->db->query ( $sql );
		if ($query) {
			return $query->result_array ();
		}
	}
	public function search_monitor($search_info, $start, $limit) {
		$sql = "select 
						media.location,
						monitor.monitor_time,
						ind.industry_name as industry_name,
						ic.industry_name as category_name,
						specification_width,
						specification_height,
						specification,
						reference_price,
						monitor.monitor_pic,
						product_name,
						sys_region.region_name as city_name,
						media.media_id,
						media_type_name,
						brand_name
						
				from 	media 
						inner join monitor on media.media_id =monitor.media_id 
						inner join sys_region on media.city =sys_region.region_id
						inner join sys_industry as ind  on monitor.industry =ind.industry_id 
						inner join sys_industry as ic  on monitor.industry_category =ic.industry_id
						inner join sys_brand on sys_brand.brand_id =monitor.brand
						inner join sys_media_type on sys_media_type.media_type_id =media.media_type_id
				where 	
						1=1 ";
		$total_sql = "select 	count(media.media_id) as total
						from 	media
								inner join monitor on media.media_id =monitor.media_id 
								inner join sys_region on media.city =sys_region.region_id
								inner join sys_industry as ind  on monitor.industry =ind.industry_id 
								inner join sys_industry as ic  on monitor.industry_category =ic.industry_id
								inner join sys_brand on sys_brand.brand_id =monitor.brand
								inner join sys_media_type on sys_media_type.media_type_id =media.media_type_id
						where 	1=1 ";
		$condition = "";
		if ($search_info ['media_type']) {
			$media_type = $this->db->escape ( $search_info ['media_type'] );
			$condition .= " and  media_type =$media_type";
		}
		// 判断搜索类型
		if ($search_info ['search_name_type'] == "seasch_brand") {
			// 按照品牌搜索
			$brand_id = $this->db->escape ( $search_info ['brand_id'] );
			$condition .= " and  brand_id = $brand_id";
		} elseif ($search_info ['search_name_type'] == "seasch_product") {
			// 在品牌中搜索产品
			$brand_id = $this->db->escape ( $search_info ['brand_id'] );
			$product_name = $this->db->escape ( $search_info ['brand_name'] );
			$condition .= " and  brand_id = $brand_id";
			$condition .= " and  product_name = $product_name";
		} else {
			// 仅按照名称搜索
			if ($search_info ['brand_name']) {
				$brand_name = $this->db->escape_like_str ( $search_info ['brand_name'] );
				$condition .= " and ( brand_name like '%$brand_name%' or product_name like '%$brand_name%')";
			}
		}
		if ($search_info ['province']) {
			$province = $this->db->escape ( $search_info ['province'] );
			$condition .= " and  province =$province";
		}
		if ($search_info ['city']) {
			$city = $this->db->escape ( $search_info ['city'] );
			$condition .= " and  city =$city";
		}
		if ($search_info ['area']) {
			$area = $this->db->escape ( $search_info ['area'] );
			$condition .= " and  region =$area";
		}
		if ($search_info ['highway']) {
			$highway = $this->db->escape ( $search_info ['highway'] );
			$condition .= " and  highway =$highway";
		}
		if ($search_info ['airport']) {
			$airport = $this->db->escape ( $search_info ['airport'] );
			$condition .= " and  airport =$airport";
		}
		if ($search_info ['start_date']) {
			$start_date = $this->db->escape ( $search_info ['start_date'] );
			$condition .= " and  date(monitor_time) >=$start_date";
		}
		if ($search_info ['end_date']) {
			$end_date = $this->db->escape ( $search_info ['end_date'] );
			$condition .= " and  date(monitor_time) <=$end_date";
		}
		if ($search_info ['industry']) {
			$industry = $this->db->escape ( $search_info ['industry'] );
			$condition .= " and  industry =$industry";
		}
		if ($search_info ['industry_category']) {
			$industry_category = $this->db->escape ( $search_info ['industry_category'] );
			$condition .= " and  industry_category =$industry_category";
		}
		if ($search_info ['remove_duplicate']) {
			$condition .= " group by media.media_id ";
			$total_sql = "select 	count(DISTINCT(media.media_id)) as total
						from 	media
								inner join monitor on media.media_id =monitor.media_id 
								inner join sys_region on media.city =sys_region.region_id
								inner join sys_industry as ind  on monitor.industry =ind.industry_id 
								inner join sys_industry as ic  on monitor.industry_category =ic.industry_id
								inner join sys_brand on sys_brand.brand_id =monitor.brand
								inner join sys_media_type on sys_media_type.media_type_id =media.media_type_id
						where 	1=1 ";
		}
		$total_sql = $total_sql . $condition;
		
		$sql = $sql . $condition . " limit $start,$limit";
		
		$total_query = $this->db->query ( $total_sql );
		$result ['total'] = 0;
		$result ['list'] = array ();
		if ($total_query) {
			$row = $total_query->row_array ();
			$result ['total'] = $row ['total'];
			$query = $this->db->query ( $sql );
			$result ['list'] = $query->result_array ();
		}
		return $result;
	}
}